package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.oocl.dao.CustomerDao;
import com.oocl.pojo.Customer;


@Repository("cDao")
public class CustomerDaoImpl  implements CustomerDao{
    private DataSource dataSource;

    @Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
    
	@Override
	public Customer addCustomer(Customer c) throws SQLException{
		String sql="insert into customer(id,cname,sex,sal,birth) values (?,?,?,?,?)";
		Connection conn=dataSource.getConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		String id=UUID.randomUUID().toString();
		c.setId(id);
		int m=0;
		pst.setString(1,id);
		pst.setString(2, c.getCname());
		pst.setBoolean(3,c.getSex());
		pst.setDouble(4,c.getSal() );
		pst.setDate(5, new Date(c.getBirth().getTime()));
		m=pst.executeUpdate();
		pst.close();
		conn.close();
		return c;
	}


	@Override
	public List<Customer> findAllCustomers() throws SQLException{
		List<Customer> list = new ArrayList<Customer>();
		String sql="select * from customer";
		Connection conn=dataSource.getConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()){
			Customer c=new Customer();
			c.setId(rs.getString("id"));
			c.setCname(rs.getString("cname"));
			c.setSex(rs.getBoolean("sex"));
			c.setSal(rs.getDouble("sal"));
			c.setBirth(rs.getDate("birth"));
			list.add(c);
		}
		rs.close();
		pst.close();
		conn.close();
		
		return list;
	}


	@Override
	public Customer deleteCustomer(String id) throws SQLException{
		Customer c = null;
		String sql="delete from customer where id=?";
		Connection conn=dataSource.getConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1, id);
		int m=pst.executeUpdate();
		pst.close();
		conn.close();
		if(m>0){
			c = new Customer();
		}
		return c;
	}


	@Override
	public Customer FindCustomerById(String id) throws SQLException{
		String sql="select * from customer where id = ?";
		Customer c=new Customer();
		Connection conn=dataSource.getConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		pst=conn.prepareStatement(sql);
		pst.setString(1,id);
		rs=pst.executeQuery();
		if(rs.next()){
			c.setId(rs.getString("id"));
			c.setCname(rs.getString("cname"));
			c.setSex(rs.getBoolean("sex"));
			c.setSal(rs.getDouble("sal"));
			c.setBirth(rs.getDate("birth"));
		}
		rs.close();
		pst.close();
		conn.close();
		return c;
	}


	@Override
	public Customer updateCustomer(Customer c) throws SQLException{
		String sql="update customerset cname=?,sex=?,sal=?,birth=? where id = ?";
		Connection conn=dataSource.getConnection();
		PreparedStatement pst=null;
		pst=conn.prepareStatement(sql);
		int m=0;
		pst.setString(1, c.getCname());
		pst.setBoolean(2,c.getSex());
		pst.setDouble(3,c.getSal() );
		pst.setDate(4, new Date(c.getBirth().getTime()));
		pst.setString(5,c.getId());
		m=pst.executeUpdate();
		pst.close();
		conn.close();
		if(m>0)  return c;
		return null;
	}
    
}
